package com.huang.mybatis.jdbc.dao;

import com.huang.mybatis.pojo.User;
import lombok.val;

import java.sql.*;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

/**
 * @Author: huang
 * @Date: 2021/8/3 20:37
 * @Description: 从数据库查找大于某个年龄的用户
 */
public class UserJDBCDao {
    public List<User> findUserLIstGtAge(int age) {
        String url = "jdbc:mysql://127.0.0.1:3306/god?characterEncoding=utf8&useSSL=false";
        String password = "huang990808";
        String username = "root";
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;
        Connection connection = null;
        try {
            //1.加载JDBC驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2.建立获取数据库连接
            connection = DriverManager.getConnection(url, username, password);
            //3.创建JDBC Statements 对象
            //4.设置sql语句的传入参数
            String sql = "select * from g_user where age > ?";
            preparedStatement = connection.prepareStatement(sql);
            //4.传入参数
            preparedStatement.setInt(1, age);
            //5.执行sql语句
            resultSet = preparedStatement.executeQuery();
            //6.对查询结果进行转换并处理返回
            List<User> userList = new ArrayList<>();
            while (resultSet.next()) {
                long id = resultSet.getLong("id");
                String username1 = resultSet.getString("username");
                int age1 = resultSet.getInt("age");
                String idCard = resultSet.getString("id_card");
                User user = new User();
                user.setId(id);
                user.setAge(age1);
                user.setUsername(username);
                user.setIdCard(idCard);
                userList.add(user);
            }
            return userList;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
                if (connection != null) {
                    connection.close();
                }
                } catch(SQLException throwables){
                    throwables.printStackTrace();
                }
            }
        return Collections.emptyList();
    }
}
